package com.groot.medical.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.groot.common.util.MedicalDBUtil;
import com.groot.common.util.SimpleDateUtil;
import com.groot.medical.entity.ChronicDisease;
import com.groot.medical.entity.FamilyArchives;
import com.groot.medical.entity.Pay;
import com.groot.medical.entity.PersonalFiles;

public class FamilyArchivesDaoImpl implements FamilyArchivesDao {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	@Override
	public int getAllFamilyCount() {
		int i =0;
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
		conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
		String sql="SELECT count(1) AS id FROM t_family_rchives";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		if(rs.next()){
			i=rs.getInt("id");
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return i;
	}

	@Override
	public List<FamilyArchives> findFamilyByPage(int startIndex, int pageSize) {
	
		
		String sql = "select * from t_family_rchives limit ?,?";
		
		try {
			conn = MedicalDBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, startIndex);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			List<FamilyArchives> familys = new ArrayList<>();
			
			while(rs.next()){
				/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
				FamilyArchives family = new FamilyArchives();
				family.setContryCode(rs.getString(1));
				family.setTownCode(rs.getString(2));
				family.setVillageCode(rs.getString(3));
				family.setGroupCode(rs.getString(4));
				family.setFamilyCode(rs.getString(5));
				family.setFamilyProperty(rs.getString(6));
				family.setFamilyHolder(rs.getString(7));
				family.setFamilyPersonCounts(rs.getInt(8));
				family.setAgriculturePersonCounts(rs.getInt(9));
				family.setFamilyAddress(rs.getString(10));
				family.setCreateRchivesTime(rs.getDate(11));
				family.setRegistrarer(rs.getString(12));
				
				familys.add(family);
				
			}
			
			return familys;
			
		} catch (SQLException e) {
			e.printStackTrace();
			
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return null;
	}
	private java.sql.Date fomart(java.util.Date data){
		return new java.sql.Date(data.getTime());
	}
	@Override
	public int addFamilyInfo(FamilyArchives family) {
		int i =0;
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="insert into t_family_rchives(contry_code,town_code,village_code,group_code,family_code,family_property,family_holder,family_person_counts,agriculture_person_counts,family_address,create_rchives_time,registrar)values(?,?,?,?,?,?,?,?,?,?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1,family.getContryCode());
			ps.setString(2, family.getTownCode());
			ps.setString(3, family.getVillageCode());
			ps.setString(4,family.getGroupCode());
			ps.setString(5, family.getFamilyCode());
			ps.setString(6, family.getFamilyProperty());
			ps.setString(7,family.getFamilyHolder());
			ps.setInt(8, family.getFamilyPersonCounts());
			ps.setInt(9, family.getAgriculturePersonCounts());
			ps.setString(10,family.getFamilyAddress());
			ps.setDate(11, fomart(family.getCreateRchivesTime()));
			ps.setString(12, family.getRegistrarer());
			i= ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
			return i;
	}
	
	@Override
	public String aotuCreateFamilyCode(FamilyArchives family) {
		
		String sql="SELECT MAX(family_code) FROM t_family_rchives where group_code='"+family.getGroupCode()+"' " ;
	
		try{
			conn=MedicalDBUtil.getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			String maxcode="";
			Integer number=1;
			if(rs.next()){
			   maxcode=rs.getString(1);
			}
			
			if(maxcode!=null){
			  int beginIndex=maxcode.length()-2;
			  String no=maxcode.substring(beginIndex);
			  number=Integer.parseInt(no);
			  ++number;
			  //使用0补足位数
			  no=String.format("%04d", number);
			  maxcode=family.getGroupCode()+no;
			}else{
				String no=String.format("%04d", number);
				maxcode=family.getGroupCode()+no;
			}
			return maxcode;
		}catch(SQLException e){
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}

	@Override
	public String findMaxFamilyCode(String groupArea) {
		String sql="SELECT MAX(family_code) FROM t_family_rchives where group_code='"+groupArea+"'" ;
		
		try{
			conn=MedicalDBUtil.getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			String maxcode="";
			
			if(rs.next()){
			   maxcode=rs.getString(1);
			}
			
			
			return maxcode;
		}catch(SQLException e){
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}
	
	@Override
	public FamilyArchives findFamilyInfoByFamilyCode(String familyCode) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String sql = "select * from t_family_rchives where family_code = ? ";
		
		try {
			ps = conn.prepareStatement(sql);
		    ps.setString(1, familyCode);
			rs = ps.executeQuery();
			
			FamilyArchives family = null;
			while(rs.next()){
				/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
				family= new FamilyArchives();
				family.setContryCode(rs.getString(1));
				family.setTownCode(rs.getString(2));
				family.setVillageCode(rs.getString(3));
				family.setGroupCode(rs.getString(4));
				family.setFamilyCode(rs.getString(5));
				family.setFamilyProperty(rs.getString(6));
				family.setFamilyHolder(rs.getString(7));
				family.setFamilyPersonCounts(rs.getInt(8));
				family.setAgriculturePersonCounts(rs.getInt(9));
				family.setFamilyAddress(rs.getString(10));
				family.setCreateRchivesTime(rs.getDate(11));
				family.setRegistrarer(rs.getString(12));
				
				
			}
			
			return family;
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}

	@Override
	public List<PersonalFiles> findFamilyAllPersonByFamilyCode(String familyCode) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String sql = "select * from t_personnel_file where family_code = ? ";
		
		try {
			ps = conn.prepareStatement(sql);
		    ps.setString(1, familyCode);
			rs = ps.executeQuery();
			List<PersonalFiles> persons = new ArrayList<>();
			
			while(rs.next()){
				/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
				PersonalFiles person= new PersonalFiles();
				person.setFamilyCode(rs.getString(1));
				person.setFarmingcardCode(rs.getString(2));
				person.setMedicalcardCode(rs.getString(3));
				person.setName(rs.getString(4));
				person.setHolderRelation(rs.getString(5));
				person.setIdcardNumber(rs.getString(6));
				person.setSex(rs.getString(7));
				person.setHealth(rs.getString(8));
				person.setNation(rs.getString(9));
				person.setCultrueDegree(rs.getString(10));
				person.setBirthday(rs.getDate(11));
				person.setPersonProperty(rs.getString(12));
				person.setIsContryside(rs.getString(13));
				person.setOccupation(rs.getString(14));
				person.setWorkAddress(rs.getString(15));
				person.setPhoneNumber(rs.getString(16));
				person.setOftenAddress(rs.getString(17));
				person.setIsMarry(rs.getString(18));
				person.setFamilyInCode(rs.getString(19));
				persons.add(person);
				
				
			}
			
			return persons;
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}

	@Override
	public List<FamilyArchives> findFamilyListByFamilyCode(String familyCode) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String sql = "select * from t_family_rchives where family_code = ? ";
		
		try {
			ps = conn.prepareStatement(sql);
		    ps.setString(1, familyCode);
			rs = ps.executeQuery();
			 
			List<FamilyArchives> familys = new ArrayList<>();
			
			while(rs.next()){
				/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
				FamilyArchives family= new FamilyArchives();
				family.setContryCode(rs.getString(1));
				family.setTownCode(rs.getString(2));
				family.setVillageCode(rs.getString(3));
				family.setGroupCode(rs.getString(4));
				family.setFamilyCode(rs.getString(5));
				family.setFamilyProperty(rs.getString(6));
				family.setFamilyHolder(rs.getString(7));
				family.setFamilyPersonCounts(rs.getInt(8));
				family.setAgriculturePersonCounts(rs.getInt(9));
				family.setFamilyAddress(rs.getString(10));
				family.setCreateRchivesTime(rs.getDate(11));
				family.setRegistrarer(rs.getString(12));
				
				familys.add(family);
				
			}
			
			return familys;
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}

	@Override
	public int getAllFamilyCountByLike(String holdername, String townCode) {
		int count = 0;
        //sql拼接
		
		StringBuffer sql = new StringBuffer("SELECT COUNT(1) AS id FROM t_family_rchives where 1=1");
	
        
        if(townCode != null && !townCode.trim().equals("")){
        	sql.append(" AND town_code like '%"+townCode+"%'");
        }if(holdername != null && !holdername.trim().equals("")){
        	sql.append(" AND family_holder like '%"+holdername+"%'");
        }
        //查询结果
        try {
        	 conn = MedicalDBUtil.getConnection();
             ps = conn.prepareStatement(sql.toString());
             rs = ps.executeQuery();
            if(rs.next()) {
                count = rs.getInt("id");
            }
        } catch (SQLException e) {

    		e.printStackTrace();
    		}
    		finally{
    			MedicalDBUtil.closeAll(conn, ps, rs);
    		}
        return count;

	}

	@Override
	public List<FamilyArchives> findByPageAndLike(int startIndex, int pageSize, String townCode, String holdername) {
    StringBuffer sql = new StringBuffer("select * from t_family_rchives where 1=1");
		
		if(townCode != null && !townCode.trim().equals("")){
        	sql.append(" AND family_code like '%"+townCode+"%'");
        }if(holdername != null && !holdername.trim().equals("")){
        	sql.append(" AND family_holder like '%"+holdername+"%'");
        }
        sql.append(" limit ?,? " );
        try {
			conn = MedicalDBUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, startIndex);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			List<FamilyArchives> familys = new ArrayList<>();
			
			while(rs.next()){
				FamilyArchives family= new FamilyArchives();
				family.setContryCode(rs.getString(1));
				family.setTownCode(rs.getString(2));
				family.setVillageCode(rs.getString(3));
				family.setGroupCode(rs.getString(4));
				family.setFamilyCode(rs.getString(5));
				family.setFamilyProperty(rs.getString(6));
				family.setFamilyHolder(rs.getString(7));
				family.setFamilyPersonCounts(rs.getInt(8));
				family.setAgriculturePersonCounts(rs.getInt(9));
				family.setFamilyAddress(rs.getString(10));
				family.setCreateRchivesTime(rs.getDate(11));
				family.setRegistrarer(rs.getString(12));
				familys.add(family);
				
			}
			
			return familys;
			
		} catch (SQLException e) {
			e.printStackTrace();
			
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return null;
	}

	@Override
	public int findFamilyByFamilyCode(String familyCode) {
		int personCounts=0;
		
        //sql拼接
		
	StringBuffer sql = new StringBuffer("SELECT COUNT(family_code) id FROM t_personnel_file where  family_code='"+familyCode+"'");
	
        
       
        //查询结果
        try {
        	 conn = MedicalDBUtil.getConnection();
             ps = conn.prepareStatement(sql.toString());
             rs = ps.executeQuery();
            if(rs.next()) {
            	personCounts = rs.getInt("id");
            }
        } catch (SQLException e) {

    		e.printStackTrace();
    		}
    		finally{
    			MedicalDBUtil.closeAll(conn, ps, rs);
    		}
        return personCounts;
		
	}

	@Override
	public void updataPersonCountsByFamilyCode(int personCounts, String familyCode) {
		int i= personCounts++;
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			//UPDATE student SET name='"+name+"', age=" + age+" where name='大红'";
			String sql="update t_family_rchives set family_person_counts=?,agriculture_person_counts=? where family_code=?";
			ps=conn.prepareStatement(sql);
			
			ps.setInt(1, i);
			ps.setInt(2, i);
			ps.setString(3,familyCode);
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}

	@Override
	public void ediFamilyInfo(FamilyArchives family) {
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			//UPDATE student SET name='"+name+"', age=" + age+" where name='大红'";
			String sql="update t_family_rchives set contry_code=?,town_code=?,village_code=?,group_code=?,family_property=?,family_holder=?,family_person_counts=?,agriculture_person_counts=?,family_address=?,create_rchives_time=?,registrar=? where family_code=?";
			ps=conn.prepareStatement(sql);
			int index=1;
			ps.setString(index++,family.getContryCode());
			ps.setString(index++,family.getTownCode());
			ps.setString(index++,family.getVillageCode());
			ps.setString(index++,family.getGroupCode());
			ps.setString(index++,family.getFamilyProperty());
			ps.setString(index++,family.getFamilyHolder());
			ps.setInt(index++,family.getFamilyPersonCounts());
			ps.setInt(index++,family.getAgriculturePersonCounts());
			ps.setString(index++,family.getFamilyAddress());
			ps.setDate(index++,SimpleDateUtil.fomart(family.getCreateRchivesTime()));
			ps.setString(index++,family.getRegistrarer());
			ps.setString(index++,family.getFamilyCode());
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}

	@Override
	public int deleteFamily(String familyCode) {
		int i=0;
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="delete from t_family_rchives where family_code=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, familyCode);
			i=ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		return i;
	}

}
